package com.cpi.cornpp.instructionsforecasts.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.instructionsforecasts.dto.InstructionsForecastsDTO;
import com.cpi.cornpp.instructionsforecasts.exception.InstructionsForecastsException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;
import com.cs.common.utils.TimeUtils;

/**
 * 
 * @description: This Class is used to handle the Instructions Forecasts Data Access
 * @author Edgar Joao
 */
public class InstructionsForecastsDAO extends DBDAO {
	private static final String SELECT_INSTRUCTIONS_FORECASTS = "exec sp_global_instructions_forecasts ?, ? ";//$NON-NLS-1$
	/** SQL Insert Instruction Forecast */ 
	private static final String SQL_INSERT_INSTRUCTION_FORECAST = "INSERT INTO instructions_forecasts(purchase_instruction_detail_id, month_period_id, qty_item_contracted, qty_item_non_contracted, qty_corn_contracted, qty_corn_non_contracted,   added) VALUES (?,?,?,?,?,?,?)";//$NON-NLS-1$
	/** SQL delete Instruction Forecast */ 
	private static final String SQL_DELETE_INSTRUCTION_FORECAST = "DELETE FROM instructions_forecasts WHERE instruction_forecast_id = ? ";//$NON-NLS-1$
	/**
	 * Devuelve la lista de forecast en base a un customerid, itemId e instructionforecast
	 * @author Edgar Joao
	 * @param instructionForecast
	 * @param customerId
	 * @param itemId
	 * @return
	 * @throws InstructionsForecastsException
	 */
	public List getInstructionsForecasts( String customerId, int purchaseInstructionDetailId) throws InstructionsForecastsException{
		List listOfInstructionsForecasts = null;
		PreparedStatement preparedStatement = null;
		InstructionsForecastsDTO instructionsForecastsDTO = null;
		StringBuffer sql = null;
		ResultSet rs = null;
		try{
			
			sql = new StringBuffer(0);
			sql.append(SELECT_INSTRUCTIONS_FORECASTS);
			
			listOfInstructionsForecasts = new ArrayList(0);			
			preparedStatement = prepareSQL(sql.toString(), dsName);
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			preparedStatement.setString(2, customerId);
			
			rs = executeQuery(preparedStatement);
			while (rs.next()){
				instructionsForecastsDTO = new InstructionsForecastsDTO();
				instructionsForecastsDTO.setPurchaseInstructionDetailId(rs.getInt(1));
				instructionsForecastsDTO.setInstructionForecastId(rs.getInt(2));
				instructionsForecastsDTO.setCustomerId(rs.getString(3));
				instructionsForecastsDTO.setItemId(rs.getString(4));
				instructionsForecastsDTO.setMonthPeriodId(rs.getInt(5));
				instructionsForecastsDTO.setForecastAdded(rs.getInt(14));
				instructionsForecastsDTO.setYear(rs.getInt(6));
				instructionsForecastsDTO.setMonth(rs.getInt(7));
				instructionsForecastsDTO.setContracted(rs.getDouble(9));
				instructionsForecastsDTO.setCornContracted(rs.getDouble(10));
				instructionsForecastsDTO.setNonContracted(rs.getDouble(11));
				instructionsForecastsDTO.setCornNonContracted(rs.getDouble(12));
				instructionsForecastsDTO.setFutures(rs.getDouble(17));
				instructionsForecastsDTO.setActual(rs.getDouble(16));
				listOfInstructionsForecasts.add(instructionsForecastsDTO);
			}	
		}catch (PoolException poolException){
			InstructionsForecastsException instructionsForecastsException = null;
			poolException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw instructionsForecastsException;	
		} catch (SQLException sqlException) {
			InstructionsForecastsException instructionsForecastsException = null;
			sqlException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw instructionsForecastsException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfInstructionsForecasts;		
	}
	
	/**
	 * 
	 * @param customerId
	 * @param itemId
	 * @return
	 * @throws InstructionsForecastsException
	 */
	public List getInstructionsForecasts(String customerId) throws InstructionsForecastsException{
		return getInstructionsForecasts(customerId, -1);
	}

	/**
	 * Inserta un instruction forecast
	 * @param dto
	 */
	public void insertInstructionsForecast(InstructionsForecastsDTO dto) throws InstructionsForecastsException {
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL( SQL_INSERT_INSTRUCTION_FORECAST , dsName);			
			preparedStatement.setInt(1, dto.getPurchaseInstructionDetailId());
			preparedStatement.setInt(2, dto.getMonthPeriodId());
			preparedStatement.setDouble(3, dto.getContracted());
			preparedStatement.setDouble(4, dto.getNonContracted());
			preparedStatement.setDouble(5, dto.getCornContracted());
			preparedStatement.setDouble(6, dto.getCornNonContracted());			
			preparedStatement.setTimestamp(7, TimeUtils.actualSQLTimeStamp());
			
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				InstructionsForecastsException instructionsForecastsException = null;
				instructionsForecastsException = new InstructionsForecastsException (MSG_NO_INSERT,InstructionsForecastsException.LAYER_DAO,InstructionsForecastsException.ACTION_INSERT);				
				throw instructionsForecastsException;				
			}		
		}catch (PoolException poe) {
			InstructionsForecastsException ife = new InstructionsForecastsException(poe, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			poe.printStackTrace(System.out);
			throw ife;
		} catch (SQLException sqle){
			InstructionsForecastsException ife = new InstructionsForecastsException(sqle, InstructionsForecastsException.LAYER_DAO, InstructionsForecastsException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw ife;
		} finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqle){
			}
		}	
	}
	
	/**
	 * Borra un instruction forecast
	 * @param instructionForecastId
	 * @throws InstructionsForecastsException
	 */
	public void deleteInstructionsForecast(int instructionForecastId)throws InstructionsForecastsException{
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_DELETE_INSTRUCTION_FORECAST, dsName);
			pstm.setInt(1, instructionForecastId);			
			executeUpdate(pstm);			
		}catch (PoolException poolException){
			InstructionsForecastsException instructionsForecastsException = null;
			poolException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException (poolException, InstructionsForecastsException.LAYER_DAO,InstructionsForecastsException.ACTION_DELETE);
			throw instructionsForecastsException;
		} catch(SQLException sqlException){
			InstructionsForecastsException instructionsForecastsException = null;
			sqlException.printStackTrace(System.out);
			instructionsForecastsException = new InstructionsForecastsException (sqlException, InstructionsForecastsException.LAYER_DAO,InstructionsForecastsException.ACTION_DELETE);
			throw instructionsForecastsException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
	}


}
